package db.jdbc;

import db.ClubAdministratorRepository;
import domain.Club;
import domain.ClubAdministrator;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import web.PaginationSupport;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 社团管理员资源库接口的jdbc实现类
 *
 * @author liujiale
 */
@Repository
public class JdbcClubAdministratorRepository implements ClubAdministratorRepository {
    private static final String INSERT_ClubAdministrator = "insert into announcement(username,password,nickname,telephone,email,createTime, gender, college, studentID,clubAdminID) values(?,?,?,?,?,?,?,?,?,?)";
    private static final String DELETE_ClubAdministrator = "delete from clubAdministrator where clubAdminID = ?";
    private static final String DELETE_club_clubAdministrators = "delete from club_clubAdministrators where clubAdminID = ?";
    private static final String DELETE_clubAdministrators_clubApplication = "delete from clubAdministrators_clubApplication where clubAdminID = ?";
    private static final String DELETE_clubAdministrators_activityApplication = "delete from clubAdministrators_activityApplication where clubAdminID = ?";
    private static final String DELETE_activity_organizers = "delete from activity_organizers where clubAdminID = ?";
    private static final String DELETE_announcement_publisher = "delete from announcement_publisher where clubAdminID = ?";

    private static final String UPDATE_ClubAdministrator = "update clubAdministrator set nickname= ?, telephone= ?, email= ?, gender = ?, college = ? , studentID = ? where clubAdminID = ?";
    private static final String UPDATE_Club = "update club set clubName= ?, college= ?, description= ? where clubID = ?";
    private static final String SELECT_ClubAdministrator = "select * from clubAdministrator";
    private static final String SELECT_Club = "select * from club";
    private static final String SELECT_ClubAdministrator_BY_PAGE = SELECT_ClubAdministrator + " order by clubAdminID  limit ? offset  ?";
    private JdbcTemplate jdbc;

    @Autowired
    public JdbcClubAdministratorRepository(JdbcTemplate jdbc) {
        this.jdbc = jdbc;
    }

    /**
     * 获取社团管理员的总数
     *
     * @return 社团管理员的数量
     */
    @Override
    public long count() {
        return jdbc.queryForInt("select count(clubAdminID) from clubAdministrator");
    }

    /**
     * 获取特定社团中社团管理员的总数
     *
     * @param clubID 社团ID
     * @return 社团中社团管理员的数量
     */
    @Override
    public long countByClubID(int clubID) {
        return jdbc.queryForInt("select count(ca.clubAdminID) from clubadministrator as ca,club_clubAdministrators as cc where cc.clubID =?  and cc.clubAdminID= ca.clubAdminID",clubID);
    }

    /**
     * 创建一个新的社团管理员
     *
     * @param clubAdministrator 新社团管理员的信息
     * @return 新创建的社团管理员
     */
    @Override
    public ClubAdministrator save(ClubAdministrator clubAdministrator) {
        jdbc.update(INSERT_ClubAdministrator, clubAdministrator.getUsername(), clubAdministrator.getPassword(),
                clubAdministrator.getNickname(), clubAdministrator.getTelephone(),
                clubAdministrator.getEmail(), clubAdministrator.getCreateTime(),
                clubAdministrator.getGender(), clubAdministrator.getCollege(),
                clubAdministrator.getStudentID(), clubAdministrator.getClubAdminID());
        return clubAdministrator;
    }

    /**
     * 删除一个社团管理员
     *
     * @param clubAdminID 需要删除的社团管理员的ID
     * @return 是否删除成功
     */
    @Override
    public boolean delete(int clubAdminID) {
        jdbc.update(DELETE_activity_organizers, clubAdminID);
        jdbc.update(DELETE_announcement_publisher, clubAdminID);
        jdbc.update(DELETE_clubAdministrators_clubApplication, clubAdminID);
        jdbc.update(DELETE_clubAdministrators_activityApplication, clubAdminID);
        return jdbc.update(DELETE_club_clubAdministrators, clubAdminID)!=0&&
                jdbc.update(DELETE_ClubAdministrator, clubAdminID) != 0;
    }

    /**
     * 根据ID查找社团管理员
     *
     * @param clubAdminID 社团管理员的ID
     * @return 对应ID的社团管理员
     */
    @Override
    public ClubAdministrator findOne(int clubAdminID) {
        ClubAdministrator clubAdministrator = null;
        try {
            clubAdministrator = jdbc.queryForObject(SELECT_ClubAdministrator + " where clubAdminID=?", new ClubAdministratorRowMapper(), clubAdminID);
        } catch (DataAccessException e) {
        }
        return clubAdministrator;
    }

    /**
     * 根据用户名查找社团管理员
     *
     * @param userName 用户名
     * @return 对应用户名的社团管理员
     */
    @Override
    public ClubAdministrator findByUserName(String userName) {
        ClubAdministrator clubAdministrator = null;
        try {
            clubAdministrator = jdbc.queryForObject(SELECT_ClubAdministrator + " where username=?", new ClubAdministratorRowMapper(), userName);
        } catch (DataAccessException e) {
        }
        return clubAdministrator;
    }

    /**
     * 根据社团管理员ID查找管理的社团
     *
     * @param clubAdminID 社团管理员ID
     * @return 管理的社团
     */
    @Override
    public Club findClubByAdminID(int clubAdminID) {
        int clubID=jdbc.queryForInt("select clubID from club_clubAdministrators where clubAdminID=?",clubAdminID);

        return jdbc.queryForObject("select* from club where clubID=?", new JdbcClubRepository.ClubRowMapper(), clubID);
    }

    /**
     * 根据用户名查找社团管理员
     *
     * @param userName 用户名
     * @param password 密码
     * @return 对应用户名的社团管理员
     */
    @Override
    public ClubAdministrator findByUserName(String userName, String password) {
        ClubAdministrator clubAdministrator = null;
        try {
            clubAdministrator = jdbc.queryForObject(SELECT_ClubAdministrator + " where username=? and _password=?", new ClubAdministratorRowMapper(), userName,password);
        } catch (DataAccessException e) {
        }
        return clubAdministrator;
    }

    /**
     * 查看社团管理员账号信息
     *
     * @param clubAdminID 社团管理员ID
     * @return 社团管理员的账号信息
     */
    @Override
    public ClubAdministrator viewAccountInfo(int clubAdminID) {
        ClubAdministrator clubAdministrator = null;
        try {
            clubAdministrator = jdbc.queryForObject(SELECT_ClubAdministrator + " where clubAdminID=?", new ClubAdministratorRowMapper(), clubAdminID);
        } catch (DataAccessException e) {
        }
        return clubAdministrator;
    }

    /**
     * 更新社团管理员信息
     *
     * @param clubAdministrator 更新后的社团管理员信息
     * @return 是否更新成功
     */
    @Override
    public boolean updateClubAdministrator(ClubAdministrator clubAdministrator) {
        return jdbc.update(UPDATE_ClubAdministrator, clubAdministrator.getNickname(),
                clubAdministrator.getTelephone(), clubAdministrator.getEmail(),
                clubAdministrator.getGender(), clubAdministrator.getCollege(),
                clubAdministrator.getStudentID(), clubAdministrator.getClubAdminID()) != 0;
    }

    /**
     * 查看社团管理员的社团信息
     *
     * @param clubAdminID 社团管理员ID
     * @return 社团管理员的社团信息
     */
    @Override
    public Club viewClubInfo(int clubAdminID) {
        Club club = null;
        try {
            int clubID = jdbc.queryForInt("select clubID from club_clubAdministrators where clubAdminID=?", clubAdminID);
            club = jdbc.queryForObject(SELECT_Club + " where clubID=?", new JdbcClubRepository.ClubRowMapper(), clubID);
        } catch (DataAccessException e) {
        }
        return club;
    }

    /**
     * 更新社团管理员的社团信息
     *
     * @param clubAdminID 社团管理员ID
     * @param club        新的社团信息
     * @return 是否更新成功
     */
    @Override
    public boolean updateClubInfo(int clubAdminID, Club club) {
        try {
            int clubID = jdbc.queryForInt("select clubID from club_clubAdministrators where clubAdminID=?", clubAdminID);
        } catch (DataAccessException e) {
        }
        return jdbc.update(UPDATE_Club, club.getClubName(), club.getCollege(), club.getDescription(), club.getClubID()) != 0;
    }


    /**
     * 获取所有社团管理员
     *
     * @return 所有社团管理员的列表
     */
    @Override
    public List<ClubAdministrator> findAll() {
        return jdbc.query(SELECT_ClubAdministrator + " order by clubAdminID", new ClubAdministratorRowMapper());
    }

    /**
     * 获取社团管理员
     *
     * @param club 社团
     * @return 社团管理员
     */
    @Override
    public List<ClubAdministrator> findAllByClub(Club club) {
        return jdbc.query("select c.* from clubAdministrator as c, club_clubAdministrators as cc where cc.clubID=? and cc.clubAdminID=c.clubAdminID order by clubAdminID", new ClubAdministratorRowMapper(),club.getClubID());
    }

    /**
     * 分页查找社团管理员
     *
     * @param pageNo   页码
     * @param pageSize 每页的数量
     * @return 社团管理员的公告列表
     */
    @Override
    public PaginationSupport<ClubAdministrator> findPage(int pageNo, int pageSize) {
        int totalCount = (int) count();
        int startIndex = PaginationSupport.convertFromPageToStartIndex(pageNo, pageSize);
        if (totalCount < 1) {
            return new PaginationSupport<ClubAdministrator>(new ArrayList<ClubAdministrator>(0), 0);
        }

        List<ClubAdministrator> items = jdbc.query(SELECT_ClubAdministrator_BY_PAGE, new ClubAdministratorRowMapper(), pageSize, startIndex);
        return new PaginationSupport<ClubAdministrator>(items, totalCount, pageSize, startIndex);
    }


    static class ClubAdministratorRowMapper implements RowMapper<ClubAdministrator> {
        /**
         * 将数据库查询结果映射到ClubAdministrator对象
         *
         * @param rs 查询结果集
         * @param i 行号
         * @return ClubAdministrator对象
         * @throws SQLException SQL异常
         */
        public ClubAdministrator mapRow(ResultSet rs, int i) throws SQLException {
            // 从结果集获取clubAdministrator字段，并构造ClubAdministrator对象
            return new ClubAdministrator(rs.getString("username"),
                    rs.getString("_password"),
                    rs.getString("nickname"),
                    rs.getString("telephone"),
                    rs.getString("email"),
                    rs.getTimestamp("createtime"),
                    rs.getBoolean("gender"),
                    rs.getString("college"),
                    rs.getString("studentID"),
                    rs.getInt("clubAdminID"));
        }
    }
}
